import os
from typing import Optional, List

from fit_py_ask_number_demo.data_managers import sqlite_manager
from fit_py_ask_number_demo.configs import config, query_model


class QueryModelManager:
    def __init__(self):
        """
        初始化 dsl_example 管理器
        """
        db_path = os.path.join(os.path.dirname(__file__), '../../database/kpi2.db')
        self.db_manager = sqlite_manager.SQLiteManager(db_path)

    def initialize_table(self):
        """
        初始化 dsl_example 表，并插入三行数据
        如果表已存在，则跳过初始化
        """
        if not self.db_manager.connection:
            self.db_manager.connect()

        try:
            cursor = self.db_manager.connection.cursor()

            # 检查表是否存在
            cursor.execute('''
            SELECT name FROM sqlite_master WHERE type='table' AND name='query_model'
            ''')
            table_exists = cursor.fetchone()

            if table_exists:
                return

            # 创建 query_model 表
            cursor.execute('''
            CREATE TABLE query_model (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                query_type TEXT NOT NULL,
                dsl_example TEXT NOT NULL,
                sql_template TEXT NOT NULL
            )
            ''')

            # 插入初始化数据
            for model in query_model.query_model_list:
                data = {
                    'query_type': model.query_type,
                    'dsl_example': str(model.question_dsl_examples),
                    'sql_template': model.sql_template
                }
                self.db_manager.create('query_model', data)

            self.db_manager.connection.commit()
            print("query_model 表初始化成功！")
        except Exception as e:
            print(f"初始化 dsl_example 表失败: {e}")

    def get_dsl_example_by_query_type(self, query_type: str) -> Optional[str]:
        """
        根据 query_type 查询 dsl_example 值
        :param query_type: 问题类型
        :return: dsl_example 值，如果未找到则返回 None
        """
        if not self.db_manager.connection:
            self.db_manager.connect()

        result = self.db_manager.read('query_model', f"query_type = '{query_type}'")
        if result:
            return result[0]['dsl_example']
        return None

    def get_sql_template_by_query_type(self, query_type: str) -> Optional[str]:
        """
        根据 query_type 查询 sql_template 值
        :param query_type: 问题类型
        :return: sql_template 值，如果未找到则返回 None
        """
        if not self.db_manager.connection:
            self.db_manager.connect()

        result = self.db_manager.read('query_model', f"query_type = '{query_type}'")
        if result:
            return result[0]['sql_template']
        return None

    def update_data_by_query_type(self, target: str, query_type: str, new_content: str) -> bool:
        """
        根据 query_type 更新 target 值
        :return: 是否成功
        """
        return self.db_manager.update(
            'query_model', {target: new_content}, f"query_type = '{query_type}'"
        )

    def list_all_data(self, target: str) -> list[dict]:
        """
        查看表中所有的 name 和对应的 dsl_example 值
        :return: 包含所有记录的列表，每行数据为一个字典
        """
        target_list = []
        all_query_models = self.db_manager.read('query_model')
        for query_model in all_query_models:
            target_list.append({
                "query_type": query_model["query_type"],
                target: query_model[target]
            })
        return target_list
